package controllers;


import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import play.mvc.Controller;

import models.Usuario;
import models.Categoria;

import models.Classe;


public class inserirBarco extends Application {

	private static Connection conexao;
	private static Statement comando;
	
	
	
	/***********************************************************************************/
	
	protected static void conectar() throws SQLException {  
		try {
			Class.forName("org.postgresql.Driver");
		} catch (ClassNotFoundException e) {
		}

			conexao = DriverManager.getConnection(  
			        "jdbc:postgresql://localhost:5432/SGRv2",  
			        "postgres",  
			        "senha");
		
			comando =  conexao.createStatement();
	}

	/***********************************************************************************/
	
	public static void inserirBarco() throws SQLException {
	
		List<Usuario> timoneiros = Usuario.recuperarPessoas("select * from pessoa where idtipo = 3 and idusuario not in ( select idusuario from relacaobarcopessoa)" );
		
		List<Classe> classes = Classe.Classes();
				
		List<Categoria> cascos = Categoria.Categorias(8);
		
		render(timoneiros, classes, cascos);
	}	
	
	/***********************************************************************************/
	
	public static void inserir(int classeid, String nome, String numero, String modelo, String ano, String corcasco, String corvela, int timoneiroid, int tipocasco) throws SQLException
	{
		conectar();
				
				comando.executeUpdate("insert into barco (" +
				"idclasse, " +
		        "nomebarco, "+
		        "numerobarco, "+
		        "modelobarco, "+        
		        "anobarco, "+
		        "corcascobarco, "+
		        "corvelabarco, "+
		        "timoneirobarco, "+
		        "tipobarco" +
		        
		        ") values (" +

		        "'" + classeid + "', " +
		        "'" + nome + "', " +
		        "'" +numero + "', " +
		        "'" +modelo + "', " +	
		        "'" +ano + "', " +	
		        "'" +corcasco + "', " +	
		        "'" +corvela + "', " +	
		        "'" +timoneiroid + "', " +		
		        "'" +tipocasco + "' " +
				")");
				
				int barcoid = 0;
				
				ResultSet reader = comando.executeQuery("select * from barco where nomebarco like '" + nome + "'" );
		    	 
				 while (reader.next())
			        { 
					 barcoid = ((int)reader.getInt("idbarco") );					 
			        }
				
				 if(barcoid > 0)
				 {
					 comando.executeUpdate("insert into relacaobarcopessoa (" +
								"idbarco, " +
						        "idusuario "+
						        
						        ") values (" +
						        "'" +barcoid + "'," +
						        "'" +timoneiroid + "'" +						        
								")");
					 
				 }
				
				visualizarBarcos.visualizarBarcos();
				
	}
	
	/*************************************************************/


}
